<?php

class ProofreadRead
{
	private $dbHandle;

	/**
	 * Append by Vanni at July 30 2009
	 */
	private $table_fields;
	private function parseDataToInsertMysqlSql($table_name,$one_record_data){/*{{{*/
		if( empty($this->table_fields[$table_name])	){
			$table_fields = Model::selectBySql("show full fields from `$table_name`");
			foreach($table_fields as $row){
				$this->table_fields[$table_name][strtolower($row['Field'])] = array(
					'type'	=> preg_replace('/\((?!\)).+\)+/','',$row['Type']),
					'null'	=> ($row['Null']=='YES'),
					'default' => mysql_escape_string($row['Default'])
				);
			}
		}

		$sql_str = '';

		foreach($one_record_data as $field=>$value){
			$field = strtolower($field);
			$type  = $this->table_fields[$table_name][$field]['type'];
			$null  = $this->table_fields[$table_name][$field]['null'];
			$default = $this->table_fields[$table_name][$field]['default'];


			switch($type){
				// int
				case 'int':
				case 'bit':
				case 'year':
				case 'bigint':
				case 'binary':
				case 'tinyint':
				case 'smallint':
					if(strlen($value)==0) {
						if($null)
						{
							$sql_str .= 'null,';
						}
						elseif(strlen($default))
						{
							$sql_str .= $default.',';
						}
						else
						{
							$sql_str .= '0,';
						}
					} else {
						$sql_str .= ((int)$value) . ',';
					}
				break;

				// decimal
				case 'float':
				case 'double':
				case 'decimal':
				case 'mediumint':
				case 'decimal':
					if(strlen($value)==0) {
						if($null)
						{
							$sql_str .= 'null,';
						}
						elseif(strlen($default))
						{
							$sql_str .= $default.',';
						}
						else
						{
							$sql_str .= '0,';
						}
					} else {
						$value = str_replace(',','',$value);
						$sql_str .= ((float)$value) . ',';
					}
				break;	

				//str
				case 'set':
				case 'text':
				case 'time':
				case 'blob':
				case 'char':
				case 'date':
				case 'enum':
				case 'varchar':
				case 'longblob':
				case 'longtext':
				case 'datetime':
				case 'tinyblob':
				case 'tinytext':
				case 'timestamp':
				case 'varbinary':
				case 'mediumblob':
				case 'mediumtext':
					if(strlen($value)==0) {
						if($null)
						{
							$sql_str .= 'null,';
						}
						elseif(strlen($default))
						{
							$sql_str .= "'$default',";
						}
						else
						{
							$sql_str .= "'',";
						}
					} else {
						$sql_str .= "'$value',";
					}
				break;

				default:
					$sql_str .= "'$value',";
				break;
			}

		}
		$sql_str = "(" . substr($sql_str,0,-1) . ")";
		return $sql_str;
	}/*}}}*/

	public function setDbHandle($dbHandle)
	{
		$this->dbHandle = $dbHandle;
	}

	###### Append by vanni at 2011-01-06  [CODE BEGIN] ############################/*{{{*/
	/**
	 * for new proofread flow
	 * 1: insert data to db2
	 * 2: update db1 audit_log and publish_log status to 'wating'
	 * 3: send all vi_id to vi server
	 * @return bool
	 * @author vanni
	 * @since modified at 2011-01-13
	 */
	private function newProofread($dataArray,$logObject,$logTableArray,$audit_log_id,$operationUser){
		$proofreadStatus	=   FALSE;

		$change_log = array();
		if (isset($dataArray->change_log)) {
			$change_log = Model::object2array($dataArray->change_log);
			unset($dataArray->change_log);
		}

		if (isset($dataArray->delete_sql)) {
			$delete_sql = $dataArray->delete_sql;
			unset($dataArray->delete_sql);
		}

		Model::$dbo['w']->getDbHandle()->beginTransaction();
		$this->dbHandle->beginTransaction();

		$callback_data['operation_user'] = $operationUser;
		$callback_data['audit_log_id']   = $audit_log_id;
		$callback_data['action']         = 'publish';
		$callback_data['change_log']     = $change_log;
		$callback_data['publish_log_ids']= array();

		try
		{
			$dataArray  =   Model::object2array($dataArray);
			$sql		=   $this->getInsertSql($dataArray);
			if (!empty($delete_sql)) {
				foreach ($delete_sql as $query) {
					array_unshift($sql, $query);
				}
			}

			if (empty($sql)) {
				Model::$dbo['w']->getDbHandle()->commit();
				$this->dbHandle->commit();
				return true;
			}

			file_put_contents('/tmp/proofread_read.sql',print_r($sql,1));
			file_put_contents('/tmp/proofread_data.txt',print_r($dataArray,1));

			$proofreadId	=   array();
			# insert proofread data to db2
			foreach($sql as $tableName  =>  $s)
			{
				#$tableName = substr($tableName,2);
				CommonUtils::exec_sql($this->dbHandle, $s);
				if (empty($dataArray[$tableName]))  continue;
				if (is_array($dataArray[$tableName]))
				{
					foreach ($dataArray[$tableName] as  $data)
					{
						if (empty($logTableArray[$tableName]))  continue;
						if ($this->proof_type == 'programme' && ('episode' == $tableName)) continue;
						$proofreadId[$tableName][$data['id']]   =   $data['id'];
					}
				}
			}

			try # write pulbish log!
			{
				foreach ($proofreadId   as  $tableName  =>  $ids)
				{
					foreach ($ids   as  $itemID)
					{
						$publish_log_id = $logObject->saveLog(AC::LOG_OP_PUBLISHING, $tableName, $itemID, NULL, $operationUser);
						$callback_data['publish_log_ids'][$tableName][] = $publish_log_id;
					}
				}
				$proofreadStatus = $this->sendToSdes($dataArray);
			}
			catch (PDOException $e) # db1 error
			{
				mail(CommonUtils::getAdminMail(), "Proofread publish_log failed[insert #1 db failed]. at server:".trim(`hostname`) , print_r($e,1));
			}
		}
		catch(PDOException $e) # db2 error
		{
			mail(CommonUtils::getAdminMail(), "Proofread failed[insert #2 db failed]. at server:".trim(`hostname`) , print_r($e,1));
		}

		try # write audit log to db1
		{
			if($proofreadStatus)
			{
				/*
				if($audit_log_id)
				{
					$old_status = Model::selectBySql('select action from audit_log where id=:id',array(':id'=>$audit_log_id));
					$new_status = $old_status==AC::LOG_OP_PUBLISH_FAILED ? $old_status : AC::LOG_OP_PUBLISHING;
					Model::updateBySql('update audit_log set action=:action where id=:id',array(':action'=>$new_status,':id'=>$audit_log_id));
				}
				 */
				# send vi data to vi server
				DB3::set($this->dbHandle);
				$is_premium = false;
				$programme_id = $this->programme_id; 
				if($change_log){  // new flow
					$callback_data['db3'] = CommonUtils::getDb3DataFromChangeLog($this->dbHandle, $change_log,$is_premium);
					$message = CommonUtils::getMediaIdsByDb3Data($this->dbHandle, $callback_data['db3'], $is_premium);
				}else{
					if($this->proof_type == 'programme') // old flow : all video & image
					{
						$message = CommonUtils::getAllMediaId($this->dbHandle,$programme_id,$is_premium);
						$callback_data['db3'] = CommonUtils::getDb3Data($programme_id,$operationUser);
					}
					else # Episode
					{
						$imitation_change_log = CommonUtils::getEpisodeChangeLogFromDataArray($dataArray);
						$callback_data['db3'] = CommonUtils::getDb3DataFromChangeLog($this->dbHandle, $imitation_change_log,$is_premium);
						$message = CommonUtils::getMediaIdsByDb3Data($this->dbHandle, $callback_data['db3'], $is_premium);
					}
				}
				$callback_data['id']   = $this->proof_type == 'programme' ? $this->programme_id : $this->episode_id; 
				$callback_data['type'] = $this->proof_type; 
				$callback_data['mis_xml_urls'] = $this->getMisXmlUrls($dataArray);
				$callback_data['programme_id'] = $programme_id;

				$message['action']        = 'publish';
				$message['programme_id']  = $programme_id;
				$message['callback_data'] = base64_encode(bzcompress(json_encode($callback_data),9));
				CommonUtils::sendDataToIiViProcess($message);
				file_put_contents('/tmp/vanni_senddata.log',print_r($message,1));
				file_put_contents('/tmp/vanni_callback_data.log',print_r($callback_data,1));

				$this->dbHandle->commit();
				Model::$dbo['w']->getDbHandle()->commit();
			}
			else # db1 error
			{
				$this->dbHandle->rollBack();
				Model::$dbo['w']->getDbHandle()->rollBack();
				if($audit_log_id)
				{
					Model::updateBySql('update audit_log set action=:action,remarks=:remarks where id=:id',array(':action'=>AC::LOG_OP_PUBLISH_FAILED,':remarks'=>'Update #2 DB failed!',':id'=>$audit_log_id));
				}
				
				foreach ($change_log as $change_log_detail) {
					change_log::updateOne($change_log_detail);
				}
			}
		}
		catch(PDOException $e)
		{ 
			if($proofreadStatus)
			{
				$this->dbHandle->rollBack();
				Model::$dbo['w']->getDbHandle()->rollBack();
			}

			foreach ($change_log as $change_log_detail) {
				change_log::updateOne($change_log_detail);
			}

			mail(CommonUtils::getAdminMail(), "Proofread failed[update #1 publish_log status failed]. at server:".trim(`hostname`) , print_r($e,1));
		}

		return $proofreadStatus;
	}
	###### [CODE END] ##########################/*}}}*/

	public function getProofread($dbHandle, $dataArray)
	{
		$logTableArray  =   array(
			'cast'					  =>  1,
			'chapter'				   =>  1,
			'episode'				   =>  1,
			'image'					 =>  1,
			'info_address'			  =>  1,
			'info_award'				=>  1,
			'info_biography'			=>  1,
			'info_general_information'  =>  1,
			'info_pageant'				=>  1,
			'info_product'			  =>  1,
			'info_q_a'				  =>  1,
			'info_recipe'			   =>  1,
			'info_song'				 =>  1,
			'programme'				 =>  1,
			'video'					 =>  1,
		);
							
		$logObject	  =   new publish_log();
		$this->dbHandle =   $dbHandle;
		#append by vanni
		if( isset($dataArray->audit_log_id) )
		{
			$audit_log_id = (int)$dataArray->audit_log_id;
			unset($dataArray->audit_log_id);
		}

		$operationUser  =   NULL;
		if (isset($dataArray->operationUser))
		{
			$operationUser  =   $dataArray->operationUser;
			unset($dataArray->operationUser);
		}
		
		$sendProofreadResult = FALSE;
		if (isset($dataArray->sendProofreadResult)) {
			$sendProofreadResult = $dataArray->sendProofreadResult;
			unset($dataArray->sendProofreadResult);
		}

		$this->proof_type = $dataArray->proof_type;
		$this->programme_id = $dataArray->programme_id;
		$this->episode_id = $dataArray->episode_id;
		unset($dataArray->proof_type);
		unset($dataArray->programme_id);
		unset($dataArray->episode_id);

		###### Append by vanni at 2011-01-06  [CODE BEGIN] ############################/*{{{*/
		return $this->newProofread($dataArray,$logObject,$logTableArray,$audit_log_id,$operationUser);
		###### [CODE END] ##########################/*}}}*/
	}

    private function getMisXmlUrls($dataArray)
    {
        $urls = array();
        $find_mis_sql = '
            SELECT DISTINCT 
            m.mis_pid AS PGM_ID, m.co_code AS CO_CODE, m.mis_eid AS EPI_PID 
            FROM 
            mis_pdes AS m INNER JOIN 
            programme_cpl AS c ON (m.co_code=c.cpl_company_code) AND (m.pdes_pid=c.programme_id)
            WHERE 
            (m.type=:type) AND (m.pdes_eid=:eid) AND (m.pdes_pid=:pid)
            ';
        $sql_param = array();

        if ($this->proof_type == 'programme') {
            $sql_param[':type'] = 'programme';
            $sql_param[':eid']  = 0;
            $sql_param[':pid']  = $this->programme_id; 
        } else {
            $sql_param[':type'] = 'episode';
            $sql_param[':eid']  = $this->episode_id; 
            $sql_param[':pid']  = $this->programme_id; 
        }
        $mis_result = Model::selectBySql($find_mis_sql,$sql_param);
        if($mis_result){
            $mis_api = new MisApi();
            foreach($mis_result as $api_param){
                //$mis_sync_rs = (array)$mis_api->getDataByApi('sendJobPublish',$api_param);
                $api_param ['API_CODE'] = 'sendJobPublish';
                $urls[] = CommonUtils::url_add_param(AC::MIS_API,$api_param); //.'?API_CODE='.$function,
            }
        }
        return $urls ;
    }

	/**
	 * send other message to MQ 
	 */
	/*
	private function sendToMq($dataArray)
	{
		$message	=   array();
		$message['action']  =   'Published';

		if (!empty($dataArray['programme'][0]['id']))
		{
			$message['type']	=   'programme';
			$message['id']	  =   $dataArray['programme'][0]['id'];
		}
		elseif (!empty($dataArray['episode'][0]['id']))
		{
			$message['type']	=   'episode';
			$message['id']	  =   $dataArray['episode'][0]['id'];
		}
		else
		{
			return  NULL;
		}

		$config = ConfigReaderFactory::getInstance()->getConfigReader(AC::CONFIG_MQ)->getConfig();
		$consumerConfig = $config->xpath("/config/consumer[@name='ProofreadResult']");

		$broker = (string) $consumerConfig[0]->broker;
		$queue  = (string) $consumerConfig[0]->queue;

		$header = array(
			'JMSPriority' => $dataArray['programme'][0]['priority'],
			'JMSXGroupID' => strtolower($message['type'].'_'.$message['id']),
		) ;

		$result = MessageHelper::sendMessage($broker, $queue, $message, $header);
		$result &= $this->sendToSdes($dataArray);
		return (boolean)$result;
	}
	 */

	/**
	 * Send programme and episode data to sdes(epg)
	 * If programme cpl_id has many record in the programme_cpl table when no need send, but write log 
	 *
	 * Send to sdes data format:
	 * array(
	 *		'programme' => array(					 // The item for ProofreadProgramme & ProofreadEpisode
	 *			'programme_id' => $programme_id,
	 *			'programme_no' => $cpl_id,
	 *			'category_id'  => $programme_tvbcom_category.category_id //first row found
	 *			'minisite_url' => $mini_url
	 *		),
	 *		'episode' => array(					   // The item for ProofreadEspiode only
	 *			'programme_no'	 => $cpl_id,
	 *			'onair_episode_no' => $id,
	 *			'synopsis_chi'	 => $description_short_zh, // description_short ? description_short : description
	 *			'synopsis_eng'	 => $description_short_en
	 *		)
	 * )
	 */
	public function sendToSdes($dataArray){/*{{{*/
		$message  = array();
		if (!empty($dataArray['programme'][0]['id']))
		{
			if(empty($dataArray['programme-tvbcom_category'][0]['category_id'])){
				error_log("P_Proofread programme({$dataArray['programme'][0]['id']}) data to sdes failure[NO CATEGORY_ID].");
				return true;
			}
			if(empty($dataArray['programme_cpl'])){
				error_log("P_Proofread programme({$dataArray['programme'][0]['id']}) data to sdes failure[NO PROGRAMME CPL_ID].");
				return true;
			}else{
				#for($i=0,$j=0; $i<count($dataArray['programme_cpl']); $j+=$dataArray['programme_cpl'][$i++]['cpl_company_code']=='TVB'){}
				#if($j>1 || $i==0){
				#	error_log("Proofread programme({$dataArray['programme'][0]['id']}) data to sdes failure[HAS MANY CPL_ID|NO CPL_ID].");
				$default_category_id = $dataArray['programme-tvbcom_category'][0]['category_id'];
				$main_category_id	= tvbcom_category::getMainCategoryId($default_category_id);
				foreach($dataArray['programme_cpl'] as $one_cpl){
					if($one_cpl['cpl_company_code']=='TVB'){
						$message['programme'][] = array(
							'category_id'	  => $default_category_id,
							'main_category_id' => $main_category_id,
							'programme_id'	 => $dataArray['programme'][0]['id'],
							'minisite_url'	 => $dataArray['programme'][0]['minisite_url'],
							'programme_no'	 => $one_cpl['cpl_id']
						);
					}
				}
				if(empty($message['programme'])){
					error_log("P_Proofread programme({$dataArray['programme'][0]['id']}) data to sdes failure[NO CPL_ID].");
					return true;
				}
			}
			#$message['programme']['category_id']	  = $dataArray['programme-tvbcom_category'][0]['category_id'];
			#$message['programme']['main_category_id'] = tvbcom_category::getMainCategoryId($dataArray['programme-tvbcom_category'][0]['category_id']);
			#$message['programme']['programme_id']	 = $dataArray['programme'][0]['id'];
			#$message['programme']['minisite_url']	 = $dataArray['programme'][0]['minisite_url'];
			#$message['programme']['programme_no']	 = $dataArray['programme_cpl'][0]['cpl_id'];
		}
		elseif (!empty($dataArray['episode'][0]['id']))
		{
			$sql = '
				select 
					p.id,p.minisite_url,c.cpl_id,c.cpl_company_code,
					(select category_id from `programme-tvbcom_category` where programme_id=p.id order by rank limit 1)as category_id
				from 
					programme_cpl c inner join programme p on c.programme_id = p.id
				where c.cpl_company_code="TVB" and p.id=(select programme_id from episode where id=:id)';
			$cpl_data = CommonUtils::exec_sql($this->dbHandle,$sql,array(':id'=>$dataArray['episode'][0]['id']));
			if(empty($cpl_data)){
				error_log("P_Proofread episode({$dataArray['episode'][0]['id']}) data to sdes failure[NO PROGRAMME CPL_ID].");
				return true;
			}
			#elseif(count($cpl_data)>1){
			#	error_log("Proofread episode({$dataArray['episode'][0]['id']}) data to sdes failure[PROGRAMME HAS MANY CPL_ID].");
			#	return true;
			#}
			if(empty($cpl_data[0]['category_id'])){
				error_log("P_Proofread episode({$dataArray['episode'][0]['id']}) data to sdes failure[PROGRAMME NO CATEGORY_ID].");
				return true;
			}

			$en_language = language::getLanguageID(AC::LANGUAGE_EN);
			$zh_language = language::getLanguageID(AC::LANGUAGE_ZH);
			$synopsis = array('chi'=>'','eng'=>'');

			if($dataArray['episode-language']){
				foreach($dataArray['episode-language'] as $row){
					if($row['episode_id'] == $dataArray['episode'][0]['id']){
						if($row['language_id'] == $zh_language){
							$synopsis['chi'] = $row['description_short'] ? $row['description_short'] : $row['description'];
						}elseif($row['language_id'] == $en_language){
							$synopsis['eng'] = $row['description_short'] ? $row['description_short'] : $row['description'];
						}
					}
				}
			}

			foreach($cpl_data as $one_cpl){
				$message['programme'][] = array(
					'category_id'	  => $one_cpl['category_id'],
					'main_category_id' => tvbcom_category::getMainCategoryId($one_cpl['category_id']),
					'programme_id'	 => $one_cpl['id'],
					'minisite_url'	 => $one_cpl['minisite_url'],
					'programme_no'	 => $one_cpl['cpl_id']
				);
				$message['episode'][] = array(
					'programme_no'	 => $one_cpl['cpl_id'],
					'onair_episode_no' => $dataArray['episode'][0]['onair_episode_no'],
					'synopsis_chi'	 => $synopsis['chi'],
					'synopsis_eng'	 => $synopsis['eng']
				);
			}
			#$message['programme']['category_id']	  = $cpl_data[0]['category_id'];
			#$message['programme']['main_category_id'] = tvbcom_category::getMainCategoryId($cpl_data[0]['category_id']);
			#$message['programme']['programme_id']	 = $cpl_data[0]['id'];
			#$message['programme']['minisite_url']	 = $cpl_data[0]['minisite_url'];
			#$message['programme']['programme_no']	 = $cpl_data[0]['cpl_id'];
			#$message['episode']['programme_no']	   = $cpl_data[0]['cpl_id'];
			#$message['episode']['onair_episode_no']   = 70;//temp for development
			#$message['episode']['onair_episode_no']   = $dataArray['episode'][0]['onair_episode_no'];
			/*
			$en_language = language::getLanguageID(AC::LANGUAGE_EN);
			$zh_language = language::getLanguageID(AC::LANGUAGE_ZH);

			foreach($dataArray['episode-language'] as $row){
				if($row['episode_id'] == $dataArray['episode'][0]['id']){
					$message['episode']['onair_episode_no'] = $dataArray['episode'][0]['onair_episode_no'];
					if($row['language_id'] == $zh_language){
						$message['episode']['synopsis_chi'] = $row['description_short'] ? $row['description_short'] : $row['description'];
					}elseif($row['language_id'] == $en_language){
						$message['episode']['synopsis_eng'] = $row['description_short'] ? $row['description_short'] : $row['description'];
					}
				}
			}
			*/
		}

		$config = ConfigReaderFactory::getInstance()->getConfigReader(AC::CONFIG_MQ)->getConfig();
		$consumerConfig = $config->xpath("/config/consumer[@name='ProofreadSDES']");

		$broker = (string) $consumerConfig[0]->broker;
		$queue  = (string) $consumerConfig[0]->queue;

		return MessageHelper::sendMessage($broker, $queue, $message);
	}/*}}}*/

	/*
    private function sendToPortal($dataArray)
    {
        if (isset($dataArray['programme'][0]['id'])) {
            $programme_id = $dataArray['programme'][0]['id'];
            $type = 'programme';
            $relational_id = $programme_id;
        } else if (isset($dataArray['episode'][0]['id'])) {
            $programme_id = $dataArray['episode'][0]['programme_id'];
            $type = 'episode';
            $relational_id = $dataArray['episode'][0]['id'];
        } else {
            return FALSE;
        }

        $last_modified_time = $this->getLastModifiedTimeFromDataArray($dataArray);
        if ($this->isDataOk($programme_id, $last_modified_time) == FALSE) {
            $this->sendToPortalFromCronJob($type, $programme_id, $relational_id, $last_modified_time);
        } else {
            //nothing change also send to portal
            $this->sendToPortalFromCronJob($type, $programme_id, $relational_id, $last_modified_time);
        }
    }
	 */

    private function getLastModifiedTimeFromDataArray($dataArray)
    {
        $last_modified_time = '0000-00-00 00:00:00';
        foreach ($dataArray as $type=>$data) {
            if (!is_array($data)) {
                continue;
            }

            foreach ($data as $row) {
                if (isset($row['modified_at'])) {
                    $last_modified_time = ($last_modified_time >= $row['modified_at']) ? $last_modified_time : $row['modified_at'];  
                }
            }
        }

        return $last_modified_time; 
    }

    private function isDataOk($programme_id, $last_modified_time)
    {
        $video_check_list = new video_check_list;
        $job = $video_check_list->findOne($programme_id);
        if (empty($job) || $job->modified_time <> $last_modified_time || $job->status == 'no') {
            return FALSE;
        } else { 
            return TRUE;
        }
    }

    private function sendToPortalFromCronJob($type, $programme_id, $relational_id, $last_modified_time)
    {
        try {
            Model::$dbo['portal_check_video_w']->beginTransaction();
            $this->video_check_list($programme_id, $last_modified_time);
            $this->task_list($type, $programme_id, $relational_id, $last_modified_time);
            Model::$dbo['portal_check_video_w']->commit();
        } catch(PDOException $e) {
            Model::$dbo['portal_check_video_w']->rollback();
        }
    }

    private function video_check_list($programme_id, $last_modified_time)
    {
        $video_check_list = new video_check_list;
        $video_check_list = $video_check_list->findOne($programme_id);
        if (empty($video_check_list)) {
            $video_check_list = new video_check_list;
            $video_check_list->programme_id = $programme_id;
            $video_check_list->status = 'no';
            $video_check_list->push_time = date('Y-m-d H:i:s');
            //$video_check_list->modified_time = $last_modified_time;
            $video_check_list->insert($video_check_list);
        } else {
            $video_check_list->programme_id = $programme_id;
            $video_check_list->status = 'no';
            $video_check_list->push_time = date('Y-m-d H:i:s');
            //$video_check_list->modified_time = $last_modified_time;
            $video_check_list->update($video_check_list);
        }
    }

    private function task_list($type, $programme_id, $relational_id, $last_modified_time)
    {
        $fields = array(
            'programme_id' => $programme_id, 
            'type' => $type, 
            'action' => 'publish', 
            'relational_id' => $relational_id, 
            'push_time' => date('Y-m-d H:i:s'),
            'modified_time' => $last_modified_time,
        );

        $task_list = new task_list;
        $task_list = $task_list->findOne(array($type, $relational_id));
        if (empty($task_list)) {
            $task_list = new task_list;
            $task_list->set((object) $fields);
            $task_list->insert($task_list);
        } else {
            $task_list->set((object) $fields);
            $task_list->update($task_list);
        }
    }

	private function getInsertSql($dataArray)
	{
		$deleteSql  =   $insertSql  =   $insertArray	=   NULL;

		$duplicate_update = array();

		foreach ($dataArray as  $tableName  =>  $data)
		{
			if (empty($data) || !is_array($data))   continue;

			// append by vanni
			// $table_keys   = CommonUtils::get_table_keys($tableName);
			$table_fields = array_keys($data[0]);
			$duplicate_update[$tableName] = '';
			foreach($table_fields as $field){
				//if(!in_array($field,$table_keys)){
					$duplicate_update[$tableName] .= " `$field`=VALUES(`$field`),";
				//}
			}
			if($duplicate_update[$tableName]){
				$duplicate_update[$tableName] = ' ON DUPLICATE KEY UPDATE ' . substr($duplicate_update[$tableName],0,-1);
			}
			array_walk_recursive($data,create_function('&$a','$a=mysql_escape_string($a);'));
			// append end.

			foreach ($data  as  $oneRecord)
			{
				if (empty($oneRecord)   ||  !is_array($oneRecord))  continue;

				# modified by vanni
				#$insertArray[$tableName][]  =   "('". implode("', '", $oneRecord). "')";
				$insertArray[$tableName][]  =   $this->parseDataToInsertMysqlSql($tableName,$oneRecord);
			}
		}

		//delete by donald
		//$delete_sql_array   =   $this->deleteRelation($dataArray);
		# check episode-video & modify $insertArray
		$videoEpisode	   =   $this->getVideoEpisode($dataArray, $insertArray);
		$insert_sql_array   =   Model::assembleInsertProofreadSql($insertArray,$duplicate_update);
		if (!empty($videoEpisode['episode']))
		{
			$insert_sql_array['episode']	=   $videoEpisode['episode'];
		}
		if (!empty($videoEpisode['episode-language']))
		{
			$insert_sql_array['episode-language']   =   $videoEpisode['episode-language'];
		}

		if(empty($delete_sql_array)) $delete_sql_array = array();
		if(empty($insert_sql_array)) $insert_sql_array = array();
		$update_sql_array = array_merge($delete_sql_array, $insert_sql_array);
		return $update_sql_array;

		/*
		$insertSql  =   Model::assembleInsertProofreadSql($insertArray,$duplicate_update);
		if (!empty($insertSql)  &&  is_array($insertSql))   $insertSql  =   implode(";\n\n", $insertSql). ';';

		$deleteSql  =   $this->deleteRelation($dataArray);
		if (!empty($deleteSql)) $insertSql  =   $deleteSql . "\n" . $insertSql;

		return  $insertSql;
		 */
	}

	private function getVideoEpisode($dataArray, &$insertArray)
	{
		$result =   array();
		if (empty($dataArray['programme'][0]['id']) ||  empty($dataArray['episode'][0]['id']))
		{
			return  $result;
		}

		$tmp['episode']					 =   $insertArray['episode'];
		$tmp['episode-language']			=   @$insertArray['episode-language'];
		$insertArray['episode']			 =   NULL;
		$insertArray['episode-language']	=   NULL;

		$insertSql  =   Model::assembleInsertProofreadSql($tmp, array());
		if (!is_array($insertSql))  return  $result;

		foreach ($insertSql as  $tableName  =>  $sql)
		{
			$result[$tableName] =   str_replace('INSERT INTO', 'INSERT IGNORE INTO', $sql);
		}
		return  $result;
	}

	/*
	private function deleteRelation($dataArray)
	{
		# init ,delete some relation!
		# delete :  programme_cpl, programme-tvbcom_category, episode-video, folder_item, folder_item-language,
		#		   image_folder-config,
		$deleteRelation =   array();
		$getFoldersCasts=   $folders	=   $casts  =   NULL;
		if (!empty($dataArray['programme'][0]['id']))
		{
			# delete programme_cpl programme-tvbcom_category
			$deleteRelation[]   =   ' DELETE FROM programme_cpl WHERE programme_id = "'. $dataArray['programme'][0]['id']. '" ';
			$deleteRelation[]   =   ' DELETE FROM `programme-tvbcom_category` WHERE programme_id = "'. $dataArray['programme'][0]['id']. '" ';

			# delete episode-video from programme includes videos
			$deleteRelation[]   =   ' DELETE FROM `episode-video` 
										WHERE video_id IN
										(
											SELECT  id
											FROM	video
											WHERE   programme_id = "'. $dataArray['programme'][0]['id']. '"
										)
									';

			# get DB #2 programme->folders & casts
			$getFolderCastSql = 'SELECT folders, casts FROM programme WHERE id = "'. $dataArray['programme'][0]['id']. '" ';
		}
		else	# Now, this case fixed is episode!
		{
			# delete episode-video from episode includes videos
			//$deleteRelation[]   =   ' DELETE FROM `episode-video` WHERE episode_id = "'. $dataArray['episode'][0]['id']. '" ';

			# get DB #2 episode->folders & casts
			$getFolderCastSql   =   ' SELECT folders,casts FROM episode WHERE id = "'. $dataArray['episode'][0]['id']. '" ';
		}

        #chapter-tvbcom_category
        $deleteRelation = array_merge($deleteRelation, $this->deleteChapterTvbcomCategory($dataArray));

		# get folders & casts
		$getFoldersCasts = CommonUtils::exec_sql($this->dbHandle, $getFolderCastSql);
		
		if (!empty($getFoldersCasts[0]['folders'])) {
			$folders = $getFoldersCasts[0]['folders'];
		}
		if (!empty($getFoldersCasts[0]['casts'])) {
			$casts = json_decode($getFoldersCasts[0]['casts'], TRUE);
		}

		# delete folder_item, folder_item-language
		if (!empty($folders)) {
			$deleteRelation[] = 'DELETE FROM `image_folder-config` WHERE folder_id IN ('. $folders. ')';
			$deleteRelation[] = 'DELETE FROM `folder_item-language` WHERE folder_id IN ('. $folders. ')';
			$deleteRelation[] = 'DELETE FROM folder_item WHERE folder_id IN ('. $folders. ')';
		}

		# delete cast -> folder_item folder_item-language
		if (!empty($casts)  &&  is_array($casts))
		{
			foreach ($casts as  $castRole   =>  $castValues)
			{
				if (is_array($castValues))
				{
					foreach ($castValues	as  $cast)
					{
						if (empty($cast['cast_id']))	continue;
						$getCastFolder	  =   ' SELECT folders FROM `cast` WHERE id = "'. $cast['cast_id']. '"';
						$getCastFolder	  =   CommonUtils::exec_sql($this->dbHandle, $getCastFolder);
						if (empty($getCastFolder['folders']))   continue;
						$getCastFolder	  =   $getCastFolder['folders'];

						$deleteRelation[]   =   ' DELETE FROM `image_folder-config` WHERE folder_id IN ('. $getCastFolder. ') ';
						$deleteRelation[]   =   ' DELETE FROM `folder_item-language` WHERE folder_id IN ('. $getCastFolder. ') ';
						$deleteRelation[]   =   ' DELETE FROM folder_item WHERE folder_id IN ('. $getCastFolder. ') ';
					}
				}
			}
		}

		return $deleteRelation;
	}
	*/

    private function deleteChapterTvbcomCategory($dataArray)
    {
        $deleteSql = $chapter_id = array();

        if (!empty($dataArray['chapter'])) {
			$chapters = $dataArray['chapter'];
            foreach ($chapters as $chapter) {
                $chapter_id[] = $chapter['id'];
            }
            $deleteSql[] = "DELETE FROM `chapter-tvbcom_category` WHERE `chapter_id` IN (" . implode(',', $chapter_id) . ")";
        }

        return $deleteSql;
    }
}
?>
